﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;

namespace BLL
{
    public class QS_PROJECTDETAILBLL : BaseBLL
    {
        /// <summary>
        /// 绑定GridView数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectBy(string proId)
        {
            string sql = "";

            if (proId == "")
            {
                sql = @"select t.n_proid,
       t.c_proname,
       decode(t.n_state, 1, '有效', '无效') state,
       t.n_num1,
       t.n_num2,
       t.c_remark,
       t.n_parentid,
       t.n_type,
       detail.pro_name,
       decode(t.n_type, 0, '主节点', 1, '分节点', 2, '子节点') n_type
  from QS_PROJECT t, QS_PROJECTDETAIL detail
  where t.n_proid=detail.n_proid
 order by t.n_parentid, t.n_num1, t.n_num2";
            }
            else
            {
                sql = string.Format(@"select t.n_proid,
       t.c_proname,
       decode(t.n_state, 1, '有效', '无效') state,
       t.n_num1,
       t.n_num2,
       t.c_remark,
       t.n_parentid,
       detail.pro_name,
       decode(t.n_type, 0, '主节点', 1, '分节点', 2, '子节点') n_type
  from QS_PROJECT t, QS_PROJECTDETAIL detail
  where t.n_proid=detail.n_proid
 and t.n_proid in
       (select d.n_proid
          from QS_PROJECTDETAIL d
         start with d.n_proid = {0}
        connect by prior d.n_proid = d.N_PARENTID)
 order by t.n_parentid, t.n_num1, t.n_num2
", proId);
            }
            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }

        /// <summary>
        /// 向下递归查询
        /// </summary>
        /// <param name="whr"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public DataTable SelectFor(string whr, string pid, string logname)
        {
            string sql = @"select d.n_proid,d.c_proname,d.n_parentid
  from QS_PROJECTDETAIL d
 where 1=1 " + whr + @"
 start with d.n_proid = " + pid + @"
connect by prior d.n_proid = d.n_parentid";

            DataTable dt = base.Query(sql, "", "", "", logname);
            return dt;
        }

        public QS_PROJECTDETAIL GetModelById(string id)
        {
            var sql = "SELECT * FROM QS_PROJECTDETAIL WHERE DETAILID = {0}";
            sql = string.Format(sql, id);
            var row = RowSingeleOfNull(sql);
            if (row == null)
            {
                return null;
            }
            return ToEntity(row);
        }

        public QS_PROJECTDETAIL Find(decimal id)
        {
            return GetModelById(id.ToString());
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <param name="win"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int insert(QS_PROJECTDETAIL model, string win, string logname)
        {
            model.DETAILID = GetSequence("SEQ_N_PRO_DETAIL_ID");
            var sql = @"INSERT INTO QS_PROJECTDETAIL(
	DETAILID,
	PRO_NUM,
	PRO_NAME,
	N_PROID
)
VALUES(
	{0},
	'{1}',
	'{2}',
	{3}
)";
            sql = string.Format(sql, model.DETAILID, model.PRO_NUM, model.PRO_NAME,
                model.N_PROID
                );
            return NoQuery(sql, logname, win, "insert");
        }

        public bool Insert(QS_PROJECTDETAIL entity)
        {
            entity.DETAILID = GetSequence("SEQ_N_PRO_DETAIL_ID");
            var sql = @"INSERT INTO QS_PROJECTDETAIL(
	DETAILID,
	PRO_NUM,
	PRO_NAME,
	N_PROID
)
VALUES(
	{0},
	'{1}',
	'{2}',
	{3}
)";
            sql = string.Format(sql, entity.DETAILID, entity.PRO_NUM, entity.PRO_NAME,
                entity.N_PROID
                );
            return NoQuery(sql) > 0 ? true : false;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <param name="win"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int Update(QS_PROJECTDETAIL model, string win, string logname)
        {
            var sql = @"UPDATE QS_PROJECTDETAIL
SET
	PRO_NUM = '{1}',
	PRO_NAME = '{2}',
	N_PROID ={3}
WHERE
	DETAILID = {0}";
            sql = string.Format(sql, model.DETAILID, model.PRO_NUM, model.PRO_NAME,
                model.N_PROID
                );
            return NoQuery(sql, win, "Update", logname);
        }

        public bool Update(QS_PROJECTDETAIL entity)
        {
            var sql = @"UPDATE QS_PROJECTDETAIL
SET
	PRO_NUM = '{1}',
	PRO_NAME = '{2}',
	N_PROID ={3}
WHERE
	DETAILID = {0}";
            sql = string.Format(sql, entity.DETAILID, entity.PRO_NUM, entity.PRO_NAME,
                entity.N_PROID
                );
            return NoQuery(sql) > 0 ? true : false;
        }

        public List<QS_PROJECTDETAIL> SelectByPage(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var dt = SelectByPageData(whereStr, orderbyStr, skip, take, out count);
            var entitys = new List<QS_PROJECTDETAIL>();
            foreach (DataRow item in dt.Rows)
            {
                entitys.Add(ToEntity(item));
            }
            return entitys;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="whereStr">条件 可为NULL</param>
        /// <param name="orderbyStr">排序 可为NULL</param>
        /// <param name="skip"></param>
        /// <param name="take"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable SelectByPageData(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var sql = "SELECT * FROM QS_PROJECTDETAIL";
            if (whereStr != null)
            {
                sql += " WHERE " + whereStr;
            }
            if (orderbyStr != null)
            {
                sql += " ORDER BY " + orderbyStr;
            }
            count = SqlCount(sql);
            return PageQuery(sql, skip, take);
        }

        public QS_PROJECTDETAIL ToEntity(DataRow row)
        {
            var entity = new QS_PROJECTDETAIL
            {
                DETAILID = Convert.ToDecimal(row["DETAILID"]),
                PRO_NUM = Convert.ToString(row["PRO_NUM"]),
                PRO_NAME = Convert.ToString(row["PRO_NAME"]),
                N_PROID = Convert.ToDecimal(row["N_PROID"])
            };
            return entity;
        }

        /// <summary>
        /// 绑定GridView数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectByData(int id)
        {
            string sql = string.Format(@"select detail.detailid,
       t.n_proid,
       t.c_proname,
       detail.pro_name,
       detail.pro_num,
       decode(t.n_state, 1, '有效', '无效') state,
       t.n_num1,
       t.n_num2,
       t.c_remark,
       t.n_parentid,
       t.n_type,
       detail.pro_name,
       decode(t.n_type, 0, '主节点', 1, '分节点', 2, '子节点') n_type
  from QS_PROJECTDETAIL detail, QS_PROJECT t
 where t.n_proid = detail.n_proid
   and t.n_proid={0}
 order by t.n_parentid, t.n_num1, t.n_num2", id);
            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }

        /// <summary>
        /// 绑定GridView数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectByData2()
        {
            string sql = @"select n_id,
       n_pro_id,
       c_value1,
       c_value2,
       c_value3,
       c_value4,
       c_value5,
       c_value6,
       c_value7,
       n_state,
       n_plan_id,
       c_value8,
       c_value9,
       c_value10
  from qs_plan_details";
            DataTable dt = base.Query(sql, "", "", "", "");

            return dt;
        }
    }
}